IF exists(SELECT * FROM dbo.sysobjects WHERE name='maxAantalLokalenKlas' AND xtype='P') 
BEGIN
	DROP PROCEDURE maxAantalLokalenKlas
END
GO

CREATE procedure maxAantalLokalenKlas
as
set nocount on
select deKlas,count(hetLokaal) as aantal,hetLokaal into #tussenTabel1
from rooster 
where left(deKlas,1)>='1' and left(deKlas,1) < '7'
and right(rtrim(hetLokaal),1)>='0' and right(rtrim(hetLokaal),1) <= '9'
group by deKlas,hetLokaal


select deKlas,max(aantal) as aantal into #tussenTabel2 from #tussenTabel1 
group by deKlas

select 1 as hoofdNr,'00000' as klas,0 as hulpNr,'<klassen>' as tekst
union
select distinct 2,deKlas,1,'<klas naam="' + isNull(deKlas,'') + '">'
from #tussenTabel2
union   
select distinct 2,t2.deKlas,2,'<lokaal>' + rtrim(isNull(t1.hetLokaal ,'')) + '</lokaal>'
from #tussenTabel2 as t2
left join #tussenTabel1 as t1
on t1.deKlas = t2.deKlas and t2.aantal=t1.aantal
union
select distinct 2,deKlas,3,'</klas>'
from #tussenTabel2
union
select 3,'zzzzz',999,'</klassen>'
order by hoofdNr,klas,hulpNr